#!usr/bin/env python
# -*- coding:utf-8 _*-
"""
@author:zhengxin
@file: ExcelUtil.py
@time: 2022/6/17  16:03
# @describe: excel工具类封装
"""
import os
import xlrd
from openpyxl import load_workbook


class SheetTypeError:
    pass


# 验证文件是否存在，存在读取，不存在报错
class ExcelReade(object):
    def __init__(self, excel_file, sheet_by):
        """
        验证文件是否存在，存在读取，不存在报错
        :param excel_file:  文件路径
        :param sheet_by: 文件sheet 名
        """
        if os.path.exists(excel_file):
            self.excel_file = excel_file
            self.sheet_by = sheet_by
            self._data = list()
        else:
            raise FileNotFoundError('文件不存在')

    # 读取 sheet方式-名称/索引
    def excel_data(self):
        """
        xlrd 方式读取
        :return: 
        """
        if not self._data:
            workbook = xlrd.open_workbook(self.excel_file)
            if type(self.sheet_by) not in [str, int]:
                raise SheetTypeError('请输入整数或字符串')
            elif type(self.sheet_by) == int:
                sheet = workbook.sheet_by_index(self.sheet_by)
            elif type(self.sheet_by) == str:
                sheet = workbook.sheet_by_name(self.sheet_by)

            # 读取sheet 内容-返回list，元素：字典；格式：[{'a':'a1','b':'b1'},{{'a':'a2','b':'b2'}}]
            # 获取首行信息
            title = sheet.row_values(0)
            # 遍历测试行，与首行组成dict，放在list
                # 循环，过滤首行，从1开始
            for col in range(1, sheet.nrows):
                col_value = sheet.row_values(col)
                # 与首行组成字典，放在list
                self._data.append(dict(zip(title, col_value)))
            # 结果返回
        return self._data


class ExcelData():
    def __init__(self, wb_name, sh_name):
        """
        openpyxl 模块操作 excel
        :param wb_name:  文件路径
        :param sh_name: 表单名字
        """
        # self.wb = load_workbook(wb_name)
        # self.sh_name = self.wb_name[sh_name]

        # 工作蒲对象
        self.wb = load_workbook(wb_name)
        # 表单对象
        self.sh_name = self.wb[sh_name]
        self.wb_name = wb_name

    def read_title(self):
        """
        openpyxl模块-读取表头数据
        :return:
        """
        titles = [i.value for i in list(self.sh_name.rows)[0] if i.value is not None]
        return titles

    def read_all_data(self):
        """
        openpyxl模块-读取表头以外的所有数据
        :return:
        """
        all_data = []
        # 遍历测试行，与首行组成dict，放在list
        titles = [i.value for i in list(self.sh_name.rows)[0] if i.value is not None]
        for item in list(self.sh_name.rows)[1:]:
            value_list = [l.value for l in item]
            # 与首行组成字典，放在list
            all_data.append(dict(zip(titles, value_list)))
        return all_data

    def write_data(self, row, column, value):
        """
        openpyxl模块-数据写入
        :param row: 写入的行
        :param column: 写入的列
        :param value: 写入的值
        :return:
        """
        try:
            # 写入数据
            self.sh_name.cell(row, column, value)
            # 保存数据
            self.wb.save(self.wb_name)
        except:
            print('写入数据失败')


if __name__ == '__main__':
    # 读取数据
    reader = ExcelReade('exam.xls', 'Sheet1')
    print(reader.excel_data())

    # 读取数据
    ecxle = ExcelData('user_id.xlsx', 'Sheet1')
    print(ecxle.read_title())
    print(ecxle.read_all_data())

    # 写入数据
    ecxle.write_data(9, 1, '通过')




